﻿using System;
using System.Collections.Generic;
using System.Linq;
using Citi.Helpers;
using Citi.OTFPC.Pep.Domain;
using System.Data;
using System.Configuration;
using System.IO;
using Citi.OTFPC.Pep.SchedulerUI.EmailSenderWcf;
using System.Text;

namespace Citi.OTFPC.Pep.SchedulerUI
{;
    public class PepAutomation : Citi.RoutineBase.IExecutableRoutine
    {
        #region Properties

        private string ConnectionStringResource { get { return ConfigurationManager.ConnectionStrings["ResourceEntities"].ConnectionString; } }
        private string PathExcel { get { return ConfigurationManager.AppSettings["PathExcel"]; } }
        private string PathInput { get { return ConfigurationManager.AppSettings["PathInput"]; } }
        private string EmailSubject { get { return ConfigurationManager.AppSettings["EmailSubject"]; } }
        private string PartialFileName { get { return ConfigurationManager.AppSettings["PartialFileName"]; } }
        private List<User> usersNotFound = new List<User>();

        readonly Dictionary<string, string> propertyList = new Dictionary<string, string>{
                                            {"Id","PEP ID" },
                                            {"ProjectName","PROJECT NAME"},
                                            {"Entity","ENTIDADE"}, 
                                            {"Branch", "FILIAL"}, 
                                            {"USDRate", "USD RATE"}, 
                                            {"ExpenseAmount", "EXPENSE AMOUNT"}, 
                                            {"InvestimentAmount", "INVESTMENT AMOUNT"}, 
                                            {"ExpenseCode1", "EXPENSE CODE 1"}, 
                                            {"ExpenseCode2", "EXPENSE CODE 2"}, 
                                            {"ExpenseCode3", "EXPENSE CODE 3"}, 
                                            {"ExpenseCode4", "EXPENSE CODE 4"}, 
                                            {"GOC","GOC"}, 
                                            {"StatusP2P","STATUS P2P"}, 
                                            {"Start","START DATE"}, 
                                            {"*Start","INITIAL END DATE"}, 
                                            {"End","ACTUAL END DATE"}, 
                                            {"ManagerSoeid", "CEP MANAGER SOE-ID"}};

        #endregion

        public void Rollback(ref RoutineBase.ICurrentAction currentAction)
        {
            DropTemporaryFiles(currentAction);
        }

        public void Start(DateTime scheduledDate, ref RoutineBase.ICurrentAction currentAction, Dictionary<string, object> parameters)
        {
            var networkUser = parameters["RobotUsername"].ToString();
            var networkPassword = parameters["RobotPassword"].ToString();
            var userNotFoundEmails = parameters["UserNotFoundEmails"].ToString();

            if (!Directory.Exists(PathExcel))
                Directory.CreateDirectory(PathExcel);

            DownloadAttachments(currentAction, networkUser, networkPassword);

            var filesToRead = new DirectoryInfo(PathExcel).GetFiles().Where(x => x.Name.Contains(PartialFileName)
                && (x.Extension == ".xls" || x.Extension == ".xlsx")).Select(x => x.FullName);

            if (!filesToRead.Any())
            {
                currentAction.Description = "No email was found. The routine was marked as executed.";
                return;
            }

            currentAction.Description = string.Format("Was found {0} email with the subject: '{1}'.", filesToRead.Count(), EmailSubject);

            currentAction.Description = "Reading files extracted.";
            var pepInputs = new List<PepInput>();

            foreach (var excelFile in filesToRead)
            {
                using (var reader = new ExcelReader(excelFile, true, true))
                {
                    var dataTable = reader.GetData("Ativos", false, 2);

                    dataTable.Rows.RemoveAt(0);

                    pepInputs.AddRange(from DataRow row in dataTable.Rows select PepInput.LoadCustomers(row));
                }

                File.Delete(excelFile);
            }

            var peps = GeneratePep(pepInputs.Where(x => x != null).Distinct().ToList(), networkUser, networkPassword);
            WriteFeeds(currentAction, peps, networkUser, networkPassword);

            EmailErrors(userNotFoundEmails);
            DropTemporaryFiles(currentAction);

            currentAction.Description = "Completed.";
        }

        private void EmailErrors(string userNotFoundEmails)
        {
            if (!usersNotFound.Any()) return;

            var users = usersNotFound.Select(x => x.Name.TrimEnd()).Distinct().OrderBy(x => x).ToList();

            var emailError = new StringBuilder("<b>The users below were not found on PEP Extraction Database. Please check. </b> ");
            emailError.AppendLine("<table><br /><br />");

            users.ForEach(x => emailError.AppendLine(string.Concat("<tr><td>", x, "</td></tr>")));

            emailError.AppendLine("</table>");

            using (var client = new EmailClient())
            {
                client.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

                var email = new Email
                {
                    ApplicationName = "PEP",
                    Subject = "PEP: Users not found",
                    BodyMessage = emailError.ToString(),
                    Importance = EnumsEmailImportance.High,
                    NeedApprovalToSend = false,
                    SentOnBehalfOfName = "LAC_BRA_FTOOLS",
                    Attachments = new List<Attachment>(),
                    Url = "",
                    Parameters = new Dictionary<string, string>(),
                    To = userNotFoundEmails.Split(';').Select(x => new EmailAddress { Description = x }).ToList()
                    //new List<EmailAddress> { new EmailAddress { Description = "ND61955" } }
                };

                client.InsertEmailInQueue(email);
            }
        }

        private void DropTemporaryFiles(RoutineBase.ICurrentAction currentAction)
        {
            currentAction.Description = "Dropping temporary files.";
            var files = new DirectoryInfo(PathExcel).GetFiles();
            foreach (var file in files)
                File.Delete(file.FullName);
        }

        private void WriteFeeds(RoutineBase.ICurrentAction currentAction, List<Domain.Pep> peps, string networkUser, string networkPassword)
        {
            var pathInputs = PathInput.Split('|');
            FileHelper.DateFormat = "dd/MM/yyyy";
            currentAction.Description = "Writing feed.";
            var fileName = string.Concat("PEP_", DateTime.Today.ToString("yyyyMMdd"));

            foreach (var pathInput in pathInputs)
            {
                currentAction.Description = string.Format("Saving file {0} in {1}", fileName, pathInput);
                FileHelper.WriteTxtFile(peps, propertyList, "|", pathInput, fileName, networkUser, networkPassword);
            }
        }

        private void DownloadAttachments(RoutineBase.ICurrentAction currentAction, string networkUser, string networkPassword)
        {
            currentAction.Description = "Looking for PEP email.";

            string path;

            using (var client = new EmailClient())
            {
                client.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

                var attachment = new DownloadAttachment
                {
                    ApplicationName = "PEP",
                    DefaultFolder = EmailHelper.DefaultFolders.Inbox,
                    // DownloadFolder = @"\\lacbra800w0273\c$\temp\EmailAttachment",
                    InitialDate = DateTime.Today.AddDays(-7),
                    Subject = EmailSubject
                };

                path = client.DownloadAttachment(attachment);
            }

            if (path == null) return;

            var serverName = path.Split(new[] { "/", @"\\", @"\" }, StringSplitOptions.RemoveEmptyEntries).First();

            using (NetworkShareAccesser.Access(serverName, networkUser, networkPassword))
            {
                var downloadedFiles = Directory.GetFiles(path);

                foreach (var file in downloadedFiles.Where(x => Path.GetExtension(x) == ".zip"))
                {
                    //var unpackDirectory = file.Replace(".zip", string.Empty);

                    if (!Directory.Exists(PathExcel))
                        Directory.CreateDirectory(PathExcel);

                    FileHelper.Decompress(file, PathExcel);

                    File.Delete(file);
                }
            }
        }

        private List<Domain.Pep> GeneratePep(List<PepInput> pepInputs, string networkUser, string networkPassword)
        {
            var user = new Domain.User { NetworkUser = networkUser, NetworkPassword = networkPassword };
            var pepIds = pepInputs.Select(x => x.ProjectNumber).Distinct();
            var listPeps = new List<Domain.Pep>();
            var users = user.SelectAll();

            foreach (var pepId in pepIds)
            {
                var peps = pepInputs.Where(x => x.ProjectNumber == pepId);

                var entities = peps.Select(x => x.Entity).Distinct().ToList();

                foreach (var entity in entities.Where(x => !string.IsNullOrEmpty(x)))
                {
                    var pepsEntity = peps.Where(x => x.Entity == entity).ToList();

                    var pep = new Domain.Pep
                    {
                        Entity = int.Parse(entity).ToString(),
                        Branch = pepsEntity.FirstOrDefault() != null ? pepsEntity.OrderBy(x => x.Branch).First().Branch : null,
                        End = pepsEntity.FirstOrDefault() != null ? pepsEntity.First().End : null,
                        Id = pepId,
                        InvestimentAmount = pepsEntity.Sum(x => x.InvestimentAmount),
                        ManagerSoeid = pepsEntity.FirstOrDefault() != null ? pepsEntity.First().ProjectManager : null,
                        ProjectName = pepsEntity.FirstOrDefault() != null ? pepsEntity.First().Business : null,
                        Start = pepsEntity.FirstOrDefault() != null ? pepsEntity.First().Start : null,
                        StatusP2P = pepsEntity.FirstOrDefault() != null ? pepsEntity.First().Status : null,
                        USDRate = pepsEntity.FirstOrDefault() != null ? pepsEntity.First().USDRate : 0
                    };

                    //var resource = new Resource.Domain.Resource(ConnectionStringResource).GetByName(pep.ManagerSoeid);

                    var usersPep = pep.ManagerSoeid.Split(new[] { '/', '|', ';', '(', ')', ':' }, StringSplitOptions.RemoveEmptyEntries);
                    User resource = null;

                    foreach (var userPep in usersPep)
                    {
                        resource = users.FirstOrDefault(x => x.Name.Trim().ToUpper() == userPep.Trim().ToUpper());
                        if (resource != null) break;
                    }

                    if (resource == null)
                    {
                        usersNotFound.Add(new User { Name = pep.ManagerSoeid });
                        continue;
                    }

                    pep.ManagerSoeid = resource.Soeid;

                    if (string.IsNullOrEmpty(pep.Id)) break;

                    //if (entities.Count() > 1) pep = new Domain.Pep(pep);
                    var expenseCodes = pepsEntity.Select(x => x.ExpenseCode).Distinct().OrderBy(x => x).ToList();

                    FillSectors(listPeps, pep, expenseCodes);
                }
            }

            var repeatedPeps = listPeps.GroupBy(x => x.Id).Where(x => x.Count() > 1);

            foreach (var repeatedPep in repeatedPeps)
            {
                var repeatedList = repeatedPep.ToList();
                foreach (var item in repeatedList)
                    item.Id = GeneratePepId(item.Id, repeatedList.IndexOf(item));
            }

            return listPeps.OrderBy(x => x.Id).ToList();
        }

        private void FillSectors(List<Domain.Pep> listPeps, Domain.Pep pep, List<string> expenseCodes)
        {
            for (int i = 0; i < Convert.ToInt16(Math.Ceiling(decimal.Divide(expenseCodes.Count(), 4))); i++)
            {
                var startSector = pep.Entity.Length;
                var pepClone = new Domain.Pep(pep);

                var expenseCode = long.Parse(expenseCodes[i * 4]).ToString();
                expenseCode = expenseCode.StartsWith(pep.Entity) ? expenseCode.Substring(startSector) : expenseCode;
                pepClone.ExpenseCode1 = pepClone.ExpenseCode2 = pepClone.ExpenseCode3 = pepClone.ExpenseCode4 = long.Parse(expenseCode);

                //TODO Apagar código quando tabela do I7 for corrigida
                //if (expenseCodes.Count() > i * 4 + 1)
                //{
                //    expenseCode = long.Parse(expenseCodes[i * 4 + 1]).ToString();
                //    expenseCode = expenseCode.StartsWith(pep.Entity) ? expenseCode.Substring(startSector) : expenseCode;
                //    pepClone.ExpenseCode2 = long.Parse(expenseCode);
                //}

                //if (expenseCodes.Count() > i * 4 + 2)
                //{
                //    expenseCode = long.Parse(expenseCodes[i * 4 + 2]).ToString();
                //    expenseCode = expenseCode.StartsWith(pep.Entity) ? expenseCode.Substring(startSector) : expenseCode;
                //    pepClone.ExpenseCode3 = long.Parse(expenseCode);
                //}

                //if (expenseCodes.Count() > i * 4 + 3)
                //{
                //    expenseCode = long.Parse(expenseCodes[i * 4 + 3]).ToString();
                //    expenseCode = expenseCode.StartsWith(pep.Entity) ? expenseCode.Substring(startSector) : expenseCode;
                //    pepClone.ExpenseCode4 = long.Parse(expenseCode);
                //}

                //TODO Tirar comentário quando for corrigida tabela no I7
                if (expenseCodes.Count() > i * 4 + 1)
                    pepClone.ExpenseCode2 = long.Parse(expenseCodes[i * 4 + 1]);

                if (expenseCodes.Count() > i * 4 + 2)
                    pepClone.ExpenseCode3 = long.Parse(expenseCodes[i * 4 + 2]);

                if (expenseCodes.Count() > i * 4 + 3)
                    pepClone.ExpenseCode4 = long.Parse(expenseCodes[i * 4 + 3]);

                listPeps.Add(pepClone);
            }
        }

        private string GeneratePepId(string id, int index)
        {
            if (!string.IsNullOrEmpty(id))
            {
                var lastChar = id.Last();
                var converted = 0;

                if (index == 0 && int.TryParse(lastChar.ToString(), out converted))
                    id = string.Concat(id, "a");
                else
                    id = string.Concat(id, Convert.ToChar((int)'a' + index));
            }

            return id;
        }

        public void Stop(ref RoutineBase.ICurrentAction currentAction)
        {
            DropTemporaryFiles(currentAction);
        }
    }
}